package com.worthtech.billportal.dao.hibernate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.worthtech.billportal.bean.CsPepJnls;
import com.worthtech.billportal.bean.WtPepJnls;
import com.worthtech.billportal.bean.WtPepJnlsId;
import com.worthtech.billportal.dao.SameWpjinfoDao;

public class SameWpjinfoDaoImpl extends HibernateDaoSupport implements SameWpjinfoDao {

	public List getHql(String tableName, int startPage, int perNo,
			String orderby) {
		String hql = "from " + tableName + " " + orderby;
		System.out.println("hql2=" + hql);
		if (perNo == 0) {
			return this.getSession().createQuery(hql).list();
		}
		return this.getSession().createQuery(hql).setFirstResult(startPage)
				.setMaxResults(perNo).list();
	}

	public String getListSql(String sql) {
		String tmp = this.getSession().createQuery(sql).list().get(0)
		.toString();
			return tmp;
	}
	
	public List<CsPepJnls> getList(String str){
		PreparedStatement ps;
		ResultSet rset = null;
		List<CsPepJnls> list = new ArrayList<CsPepJnls>();
		Connection c = this.getSession().connection();
			try {
				ps = c.prepareStatement(str);
				System.out.println(str);
				rset = ps.executeQuery();
				CsPepJnls vj = null;
				while(rset.next()){
					vj = new CsPepJnls();
					vj.setPepdate(rset.getString("pepdate")==null?"":rset.getString("pepdate").trim());
					vj.setIntcdno(rset.getString("intcdno")==null?"":rset.getString("intcdno").trim());
					vj.setOutcdno(rset.getString("outcdno")==null?"":rset.getString("outcdno").trim());
					vj.setTranamt(rset.getString("tranamt")==null?"":rset.getString("tranamt").trim());
					vj.setBit28(rset.getString("bit_28")==null?"":rset.getString("bit_28").trim());
					vj.setTrace(rset.getLong("trace"));
					list.add(vj);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list;
	}

	public void saveJNLS(CsPepJnls vj) {
		this.getHibernateTemplate().save(vj);
	}

	public int delCspepjnls() {
		try {
			Connection c = this.getSession().connection();
			PreparedStatement ps;
			String sql = "delete from Cs_Pep_Jnls";
			ps = c.prepareStatement(sql);
			ps.executeUpdate();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public List<WtPepJnls> getListWpj(String str){
		PreparedStatement ps;
		ResultSet rset = null;
		List<WtPepJnls> list = new ArrayList<WtPepJnls>();
		Connection c = this.getSession().connection();
		DecimalFormat   df = new DecimalFormat("0.00");
			try {
				ps = c.prepareStatement(str);
				System.out.println(str);
				rset = ps.executeQuery();
				WtPepJnls vj = null;
				while(rset.next()){
					vj = new WtPepJnls();
					WtPepJnlsId wid = new WtPepJnlsId();
					System.out.println("-----------["+rset.getString("pepdate")+" "+rset.getString(1));
					wid.setPepdate(rset.getString("pepdate")==null?"":rset.getString("pepdate").trim());//
					vj.setIntcdno(rset.getString("intcdno")==null?"":rset.getString("intcdno").substring(4).trim());
					vj.setOutcdno(rset.getString("outcdno")==null?"":rset.getString("outcdno").trim());
					wid.setOrgTermcde(rset.getString("org_termcde")==null?"":rset.getString("org_termcde").trim());
					vj.setTranamt(rset.getString("tranamt")==null?"":df.format(Float.valueOf(rset.getString("tranamt").trim())/100.00));
					vj.setTrancde(rset.getString("trancde")==null?"":rset.getString("trancde").trim());
					vj.setTermid(rset.getString("termid")==null?"":rset.getString("termid").trim());
					vj.setBit28(rset.getString("bit_28")==null?"":rset.getString("bit_28").trim());
					vj.setFstSdrespn(rset.getString("fst_sdrespn")==null?"":rset.getString("fst_sdrespn").trim());
					vj.setFstTrace(rset.getLong("fst_trace"));//
					vj.setId(wid);
					list.add(vj);
				}
				return list;
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;
	}
	
	
}
